/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.myapp.struts.dao;

//~--- non-JDK imports --------------------------------------------------------
import com.myapp.struts.beans.JvBean;
import com.myapp.struts.prepare.JvPrepareAL;

//~--- JDK imports ------------------------------------------------------------

import java.sql.*;

import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *   Document   : JvDao
 *   Created on : Apr 22, 2009, 2:10:46 PM
 *   Author     : K.Bala Lakshmi
 */
public class JvDao {

    JvPrepareAL jpl = new JvPrepareAL();
    private String accountHead;
    private String accountHead2;
    private ResultSet acnature;
    ArrayList al, alt;
    private String amountR;
    boolean b1, b;
    private String checkAcNature;
    Connection con;    // get the connection object reference
    Connect drv;    // get the driver object reference
    int i, count, count1;
    private int ij;
    private int ik;
    private int j;
    private String jvdate;
    PreparedStatement pstmt;
    private String resacnature;
    ResultSet rs;
    private boolean status;
    private double totalBalance;
    private String updateAchead;
    private String updateledger;
    private Vector vec;
    private double closingBalance;
    private double cb;

//  method to establish a connection to Database
    public Connection getConnect() throws Exception {
        drv = new Connect();
        con = drv.mySql();

        return con;
    }    // end of getconnection()

    public boolean insert(String jvno, String jvdate, String achead, String debit, String credit, Double debit1,
            String naration, String userid, String date, String Jv_Status)
            throws Exception {
        try {
            con = getConnect();
            con.setAutoCommit(false);
            System.out.println("INSERT METHOD AT JvDao...");

            String ss = "INSERT INTO journalvoucher VALUES(?,?,?,?,?,?,?,?,?,?,?)";

            pstmt = con.prepareStatement(ss);
            System.out.println("psmt" + pstmt);
            System.out.println("conn and stmt is created");
            pstmt.setString(1, jvno);
            pstmt.setString(2, jvdate);
            pstmt.setString(3, achead);
            pstmt.setString(4, debit);
            pstmt.setString(5, credit);
            pstmt.setDouble(6, debit1);

            if (Double.parseDouble(debit) > Double.parseDouble(credit)) {
                pstmt.setString(7, "Dr");
            } else {
                pstmt.setString(7, "Cr");
            }

            pstmt.setString(8, naration);
            pstmt.setString(9, userid);
            pstmt.setString(10, date);
            pstmt.setString(11, Jv_Status);

            // System.out.println(code);
            i = pstmt.executeUpdate();
            pstmt.close();

            if (i == 1) {
                checkAcNature = "select Description from account_head where Ahead_Name=?";
                pstmt = con.prepareStatement(checkAcNature);
                pstmt.setString(1, achead);
                acnature = pstmt.executeQuery();

                if (acnature.next() == true) {
                    resacnature = acnature.getString(1);
                }

                if (resacnature.equals("Dr")) {
                    if ((Integer.parseInt(debit) > 0) && (Integer.parseInt(credit) <= 0)) {
                        updateAchead = "update account_head set Opening_Balance=Opening_Balance+" + debit + " where Ahead_Name='" + achead + "'";
                        System.out.println("updateAchead" + updateAchead);
                        pstmt = con.prepareStatement(updateAchead);
                        j = pstmt.executeUpdate();
                    } else if ((Integer.parseInt(credit) > 0) && (Integer.parseInt(debit) <= 0)) {
                        updateAchead = "update account_head set Opening_Balance=Opening_Balance-" + credit + " where Ahead_Name='" + achead + "'";
                        System.out.println("updateAchead" + updateAchead);
                        pstmt = con.prepareStatement(updateAchead);
                        j = pstmt.executeUpdate();
                    }

                    if (j > 0) {
                        b = true;
                    } else {
                        b = false;
                    }
                } else if (resacnature.equals("Cr")) {
                    if ((Integer.parseInt(debit) > 0) && (Integer.parseInt(credit) <= 0)) {
                        updateAchead = "update account_head set Opening_Balance=Opening_Balance-" + debit + " where Ahead_Name='" + achead + "'";
                        System.out.println("updateAchead" + updateAchead);
                        pstmt = con.prepareStatement(updateAchead);
                        j = pstmt.executeUpdate();
                    } else if ((Integer.parseInt(credit) > 0) && (Integer.parseInt(debit) <= 0)) {
                        updateAchead = "update account_head set Opening_Balance=Opening_Balance+" + credit + " where Ahead_Name='" + achead + "'";
                        System.out.println("updateAchead" + updateAchead);
                        pstmt = con.prepareStatement(updateAchead);
                        j = pstmt.executeUpdate();
                    }

                    if (j > 0) {
                        b = true;
                    } else {
                        b = false;
                    }
                }

                con.commit();
                con.setAutoCommit(true);
                con.close();
                b = true;
            } else {
                con.commit();
                con.setAutoCommit(true);
                con.close();
                b = false;
            }        // end of else
        } // end of try
        catch (Exception e) {
            System.out.println("inside JvDao exception at insert" + e);

            // throw e;
        } // end of catch
        finally {
            if (con != null) {
                try {
                    if (con != null) {
                        con.close();
                    }
                } // end of try
                catch (Exception e) {
                    System.out.println("exception at con closed" + e);
                }    // end of catch
            }
        }            // end of finally

        return b;
    }    // end of insert method

    public boolean insertIntoLedger(String jvno, String jvodate, String accountHead1, String credit, String debit,
            Double total, String Jv_Status) {
        try {
            con = getConnect();

            String amountType = "";
            String rupeesc = "";
            String rupeesd = "";
            Vector jno = new Vector();
            Vector amtyp = new Vector();
            Vector rpcr = new Vector();
            Vector rpdt = new Vector();

            System.out.println("Account Head ######### " + accountHead1);

            Vector jv = getTransactionType(jvno, accountHead1);

            if (jv != null) {
                Iterator itg3 = jv.iterator();

                while (itg3.hasNext()) {

                    JvBean jb = (JvBean) itg3.next();

                    amountType = jb.getAmount_type();
                    rupeesc = jb.getCredit_Amount();
                    rupeesd = jb.getDebit_Amount();
                    System.out.println("*********Amount type" + amountType);
                    System.out.println("*********Cr" + rupeesc);
                    System.out.println("*********Dr" + rupeesd);
                    jvno = jb.getJv_No();
                    amtyp.add(amountType);

                    rpcr.add(rupeesc);
                    rpdt.add(rupeesd);
                }
            }


            if (amountType.equals("Dr")) {
                System.out.println("Dr SIDE>>>>>>>>>>>>>>>>>>>>>>>");
                Vector alist = getAccountHeadsList(accountHead1, "Cr", jvno);

                if (alist != null) {
                    Iterator it4 = alist.iterator();
                    JvBean jb1 = new JvBean();

                    while (it4.hasNext()) {
                        jb1 = (JvBean) it4.next();
                        accountHead2 = jb1.getAccount_Head();
                        System.out.println("--Account Head Dr JV" + accountHead2);
                        jvdate = jb1.getJv_Date();
                        amountR = jb1.getCredit_Amount();

                        if (Double.parseDouble(rpdt.elementAt(i).toString()) > Double.parseDouble(amountR)) {
                            totalBalance = totalBalance + (Double.parseDouble(amountR));
                        } else {
                            totalBalance = totalBalance + Double.parseDouble(rupeesd);
                        }

                        System.out.println("--amount R" + amountR);
                        System.out.println("--Dr amountn" + rupeesd);

                        if (Double.parseDouble(rupeesd) > Double.parseDouble(amountR)) {
                            status = insert(jvno, accountHead1, jvdate, "Dr", accountHead2, "JV", amountR);
                        } else {
                            status = insert(jvno, accountHead1, jvdate, "Dr", accountHead2, "JV", rupeesd);
                        }
                    }
                }

                if (status == true) {
                    System.out.println("TRUE.................");
                    return true;
                } else {
                    System.out.println("FALSE................");
                    return false;
                }
            } else if (amountType.equals("Cr")) {
                System.out.println("Cr SIDE>>>>>>>>>>>>>>>>>>>>>>>");
                Vector alist = getAccountHeadsList(accountHead1, "Dr", jvno);

                if (alist != null) {
                    Iterator it4 = alist.iterator();
                    JvBean jb1 = new JvBean();

                    while (it4.hasNext()) {
                        jb1 = (JvBean) it4.next();
                        accountHead2 = jb1.getAccount_Head();
                        System.out.println("--Account Head Dr JV" + accountHead2);
                        jvdate = jb1.getJv_Date();
                        amountR = jb1.getDebit_Amount();

                        if (Double.parseDouble(rupeesc) > Double.parseDouble(amountR)) {
                            totalBalance = totalBalance + (Double.parseDouble(amountR));
                        } else {
                            totalBalance = totalBalance + Double.parseDouble(rupeesc);
                        }

                        System.out.println("--amount R" + amountR);
                        System.out.println("--Dr amountn" + rupeesc);

                        if (Double.parseDouble(rupeesc) > Double.parseDouble(amountR)) {
                            status = insert(jvno, accountHead1, jvdate, "Cr", accountHead2, "JV", amountR);
                        } else {
                            status = insert(jvno, accountHead1, jvdate, "Cr", accountHead2, "JV", rupeesc);
                        }
                    }
                }

                if (status == true) {
                    System.out.println("TRUE.................");
                    return true;
                } else {
                    System.out.println("FALSE................");
                    return false;
                }
            }
            //}
        } catch (Exception ex) {
            Logger.getLogger(JvDao.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
    }

    public boolean insert(String vno, String accountHead, String date, String entrytype, String description,
            String vtype, String amount) {
        try {

            /** ******************************** */
            System.out.println("************Insert in ledger***********8");
            cb = getClosingBalance(accountHead);
            con = getConnect();
            updateledger = "insert into ledger values(?,?,?,?,?,?,?,?,?)";
            pstmt = con.prepareStatement(updateledger);
            pstmt.setString(1, null);
            pstmt.setString(2, vno);
            pstmt.setString(3, accountHead);
            pstmt.setString(4, date);
            pstmt.setString(5, entrytype);
            pstmt.setString(6, description);
            pstmt.setString(7, vtype);
            pstmt.setString(8, amount);
            System.out.println("@@@@cb=" + cb);
            System.out.println("@@@@amount=" + Double.parseDouble(amount));
            if (entrytype.equals("Dr")) {
                pstmt.setDouble(9, (cb + Double.parseDouble(amount)));
            } else {
                pstmt.setDouble(9, (cb - Double.parseDouble(amount)));
            }
            ij = pstmt.executeUpdate();
            pstmt.close();

            if (ij > 0) {
                b = true;
            } else {
                b = false;
            }
        } catch (Exception ex) {
            Logger.getLogger(JvDao.class.getName()).log(Level.SEVERE, null, ex);
        }

        return b;
    }

    public ArrayList list() throws Exception {
        try {
            System.out.println("inside list method at JvDao");
            con = getConnect();
            jpl = new JvPrepareAL();
            System.out.println(con);

            String query = "select * from journalvoucher where Jv_Status=1 order by Jv_No";

            pstmt = con.prepareStatement(query);
            System.out.println("pstmt............................................" + pstmt);
            System.out.println("con and stmt is created at get()");
            rs = pstmt.executeQuery();
            System.out.println("RS IS ...................." + rs);
            vec = jpl.prepareArrayList(rs);
            System.out.println("al......... is not null" + al);
            rs.close();
            pstmt.close();
            con.close();
        } // end of try
        catch (Exception e) {
            System.out.println("exception at list method");
        }    // end of catch

        return al;
    }    // end of list

    public ArrayList editlist(String jvno) throws Exception {
        try {
            con = getConnect();
            System.out.println("jvno is  ..........." + jvno);

            // System.out.println(con);
            String query = "select * from journalvoucher where Jv_No=?";

            // System.out.println("AFTER THE QUERY "+query);
            pstmt = con.prepareStatement(query);

            // System.out.println("values of prepared statement  "+pstmt);
            pstmt.setString(1, jvno);
            rs = pstmt.executeQuery();
            System.out.println("RS IS ..................." + rs);
            vec = jpl.prepareArrayList(rs);
            System.out.println("al is not null" + al);
            rs.close();
            pstmt.close();
            con.close();
        } // end of try
        catch (Exception e) {
            System.out.println("\n exception at retrieving the data" + e);
        }    // end of catch

        return al;
    }    // end of editlist method

    public boolean update(JvBean jb) throws Exception {
        try {
            Connection con = getConnect();

            con.setAutoCommit(false);

            // System.out.println("con at upadate"+con);
            String sno, sname, fname, sdob, sphoneno, smailid, spreseaddr, spermaaddr, smarks, scategory;

            System.out.println("at  Update method in journalvoucher DAO");

            String ss =
                    "update journalvoucher set Jv_Date=?,Account_Head=?,Debit_Amount=?,Credit_Amount=?,Total_Amount=?,Narration=?,CreatedBy=?,CreatedDt=?,Jv_Status=? where Jv_No=?";
            PreparedStatement pstmt = con.prepareStatement(ss);

            System.out.println("pstmt at update method" + pstmt);

            // System.out.println("conn and pstmt is created");
            String jvno = jb.getJv_No();
            String jvdate = jb.getJv_Date();
            String account = jb.getAccount_Head();
            String debit = jb.getDebit_Amount();
            String credit = jb.getCredit_Amount();
            String tamt = jb.getTotal_Amount();
            String narration = jb.getNarration();
            String cby = jb.getCreatedBy();
            String cdt = jb.getCreatedDt();

            pstmt.setString(1, jvdate);
            pstmt.setString(2, account);
            pstmt.setString(3, debit);
            pstmt.setString(4, credit);
            pstmt.setString(5, tamt);
            pstmt.setString(6, narration);
            pstmt.setString(7, cby);
            pstmt.setString(8, cdt);
            pstmt.setString(9, "1");
            pstmt.setString(10, jvno);

            // System.out.println("BEFORE EXECUTEUPDATE");
            int i = pstmt.executeUpdate();

            con.commit();
            pstmt.close();
            con.close();

            // System.out.println("i value is ......................................."+i);
            if (i == 1) {

                // System.out.println("values are updated......................");;
                b1 = true;
            } // end of if
            else {

                // System.out.println("values are not updated....................");
                b1 = false;
            }    // end of else                                           // con.close();
        } // end of try
        catch (Exception e) {
            System.out.println("INSIDE JvDao EXCEPTION AT UPDATE" + e);
        } // end of catch
        finally {
            try {
                if (con != null) {
                    con.close();
                }
            } // end of try
            catch (Exception e) {
                System.out.println("EXCEPTION AT CON CLOSED" + e);
            }    // end of catch
        }        // end of finally

        return b1;
    }    // end of update method

    public int delete(String invno) throws Exception {
        try {
            System.out.println("invno is  ..........." + invno);
            con = getConnect();
            con.setAutoCommit(false);

            String query = "update journalvoucher set Jv_Status=0 where Jv_No=?";

            pstmt = con.prepareStatement(query);

            // System.out.println("pstmt..............................."+pstmt);
            // System.out.println("city code is(ccode) ..........." +ccode);
            pstmt.setString(1, invno);
            count = pstmt.executeUpdate();

            // System.out.println("COUNT IS ......"+count);
            con.commit();
            pstmt.close();
            con.close();
            this.count = count;

            // System.out.println("inside delete()    \n");
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("exception at delete");
        } // end of catch
        finally {
            try {
                if (con != null) {
                    con.close();
                }
            } // end of try
            catch (Exception e) {
            }    // end of catch
        }                                     // end of finally

        return this.count;
    }    // end of delete method

    /* ...........................................max value method................................................................. */
    public String maxValue() throws Exception {
        int p = 0;
        String k = "";

        try {
            con = getConnect();

            String query = "select count(*) from journalvoucher ";

            pstmt = con.prepareStatement(query);
            rs = pstmt.executeQuery();

//          System.out.println("Rs  val"+rs);
            if (rs.next()) {
                p = rs.getInt(1) + 1;

//              System.out.println("p VALUE IS ....................."+p);
            }    // end of if

            k = new Integer(p).toString();

//          System.out.println("k VALUE IS .............................."+k);
            rs.close();
            pstmt.close();
            con.close();
        } // end of try
        catch (Exception e) {
            System.out.println("Exception at MAXmethod" + e);
        }    // end of catch

        return k;
    }

    public Vector getTransactionType(String Jv_No, String achead) throws Exception {
        try {
            System.out.println("jvno is  ..........." + achead);

            // System.out.println(con);
            String query = "select * from journalvoucher where Account_head=?" + " and Jv_No=? order by Jv_No";

            System.out.println("AFTER THE QUERY " + query);
            pstmt = con.prepareStatement(query);

            // System.out.println("values of prepared statement  "+pstmt);
            pstmt.setString(1, achead);
            pstmt.setString(2, Jv_No);
            rs = pstmt.executeQuery();
            System.out.println("RS IS ..................." + rs);
            vec = jpl.prepareArrayList(rs);
            System.out.println("al is not null" + vec);
            rs.close();
            pstmt.close();
        } // end of try
        catch (Exception e) {
            System.out.println("\n exception at retrieving the data" + e);
        }    // end of catch

        return vec;
    }

    public Vector getAccountHeadsList(String achead, String amttype, String jvno) throws Exception {
        try {

            rs = null;
            jpl = new JvPrepareAL();
            // System.out.println(con);
            String queryi = "select * from journalvoucher where Amount_Type='" + amttype + "'" + " and Account_Head!='" + achead + "' and Jv_No='" + jvno + "' order by Jv_Date ASC";

            System.out.println("AFTER THE QUERY ****" + queryi);
            pstmt = con.prepareStatement(queryi);

            // System.out.println("values of prepared statement  "+pstmt);
            // pstmt.setString(1, achead);
            // pstmt.setString(2, amttype);
            // pstmt.setString(3, jvno);
            rs = pstmt.executeQuery();
            System.out.println("RS IS ..................." + rs);
            vec = jpl.prepareArrayList(rs);
            System.out.println("Vec size is=******" + vec.size());
            rs.close();
            pstmt.close();
        } // end of try
        catch (Exception e) {
            System.out.println("\n exception at retrieving the data" + e);
        }    // end of catch

        return vec;
    }

    public double getClosingBalance(String achead) throws Exception {
        try {
            System.out.println("closing is  ..........." + achead);

            // System.out.println(con);
            String query = "select closingbalance from ledger where id in(" +
                    "select max(id) from ledger where accounthead=? " +
                    "  order by Receipt_Date ASC)";
            System.out.println("2----->" + query);
            // System.out.println("AFTER THE QUERY "+query);
            pstmt = con.prepareStatement(query);

            // System.out.println("values of prepared statement  "+pstmt);
            pstmt.setString(1, achead);
            rs = pstmt.executeQuery();
            System.out.println("RS IS ..................." + rs);

            if (rs.next() == true) {
                closingBalance = rs.getDouble(1);
            } else {
                closingBalance = 0;
            }
            rs.close();
            pstmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }    // end of catch

        return closingBalance;
    }
}
